Structured Data

Taliesin Beynon

Wolfram Research

Introduction

Sometimes “raw data” is unwieldy to work with

too large to display in the frontend

nested structure can be hard to discern

hard to write complex transformations of the data

if something goes wrong, it can be hard to understand why

Dataset in Mathematica 10 aims to solve these problems

displays large data nicely, formats nested structures in an intelligent way:

has concise query syntax for powerful transformations:

robust error handling:

Prelude: Lists and associations

Lists of values

Typically used to contain arbitrary number of similar elements:

{"Bob","Sue","Jim","Jane",...}

Also used to group related things together into a “tuple”:

{"Jane",5}

Associations from keys to values

Typically used to tag an arbitrary number of elements with unique “keys”:

<|"Bob"->30,"Sue"->32,"Jim"->33,"Jane"->41,...|>

Also used to group properties of a single thing together into a “struct”:

<|"Name"->"Bob","Age"->30,"Sex"->"male"|>

With Dataset, we can nest these uses of lists and associations, arbitrarily!

However, some ‘shapes’ are much more common than others

Operations on associations

Elegantly modelling data in WL using Datasetoften involves associations, so a quick recap:

Looking up values of keys

assoc=<|a->1,b->2,c->3|>

treat the association as a function:

assoc[a]

use Part syntax:

assoc[[Key[a]]]

use Lookup to look up a single value:

Lookup[assoc,a]

... or a list of values:

Lookup[assoc,{a,b}]

look up values in multiple associations:

Lookup[{assoc,assoc,assoc},a]

Lookup[{assoc,assoc,assoc},{a,b}]

use a default value if a key does not exist:

Lookup[assoc,d]

Lookup[assoc,d,0]

Counting elements

Use Counts to count many times each unique value occurs:

Counts[{a,b,a,a,a,a,b}]

Use CountsBy to count classes via a function:

CountsBy[Range[20],PrimeQ]

Grouping elements

Use GroupBy to assign elements to groups:

GroupBy[Range[20],PrimeQ]

GroupBy[Range[20],Divisors/*Length]

Apply a function to element after grouping:

GroupBy[{{a,1},{b,2},{a,3},{b,4}},First->Last]

Group repeatedly to produce associations of assocations:

GroupBy[Range[20],{PrimeQ,#<10&}]

Apply a final ‘reducer’:

GroupBy[Range[20],PrimeQ,Total]

Note that we can write CountsBy this way:

GroupBy[Range[20],PrimeQ,Length]==CountsBy[Range[20],PrimeQ]

Combining associations

assoc1=<|a->1,b->2,c->3|>;
assoc2=<|c->4,d->5,e->6|>;

The head Association ‘flattens’ out lists and other associations, so we can use it to join two existing associations:

Association[assoc1,assoc2]

Note the right-most ‘c’ won.

Use Merge to collect the values corresponding to duplicate keys:

Merge[{assoc1,assoc2},Identity]

Note ‘c’ was duplicated, the others were not.

Take the first duplicated value instead of taking all values

Merge[{assoc1,assoc2},First]

Total the values:

Merge[{assoc1,assoc2},Total]

Operations on keys

Many that mirror functions on values: KeySort, KeyMap, KeyTake,KeyDrop, etc.

For sparse datasets, we can ‘fill in’ missing keys using KeyUnion:

KeyUnion[{assoc1,assoc2},0&]

We can also extract the ‘lowest common association’ using KeyIntersection:

KeyIntersection[{assoc1,assoc2}]

Relational joins

In SQL, a fundamental operation is to join together two tables by joining rows that share the value of a given field (=key).

JoinAcrossmakes the same semantics possible in WL.

Read the documentation!

Operator forms

Version 10 introduced operator forms, which are a form of currying.

Operator forms let us cleanly represent certain functions that are ‘ready to go’, but haven’t been applied yet.

Why? Three main reasons...

Readability and composability

Here is some code to find the most frequent words in a text:

Load the text of “Alice in Wonderland”:

text=ExampleData[{"Text","AliceInWonderland"}];

Load ‘stop words’ like the, and, it, etc. We will remove these:

trivialWords=Alternatives @@ WordData[All,"Stopwords"];

Split the text up into words, remove stopwords, count them, and take teh 10 most frequent words:

words=Take[Reverse[Sort[Counts[DeleteCases[ToLowerCase[StringSplit[text]],trivialWords]]]],10]

This style of nested code is common in WL, and needs to be read ‘inside out’:

First you StringSplit[text]. 
Then you ToLowerCase the result. 
Then you DeleteCases all stopwords from that, etc.

Unfortunately there are many downsides to this style:

Doing ‘bracket surgery’ to change the order of operations or to add or remove operations is error prone and painful. Clickety click.

It’s hard to see where to start.

You have to read things inside out.

We can use syntax like @  and //  to simplify this code, but this only works for unary functions like Sort. Precedence can also be tricky...

Operator forms let us represent many such cases as readable left-to-right chains of operators:

StringSplit/*ToLowerCase /* DeleteCases[trivialWords]/*Counts/*Sort/*Reverse/*Function[Take[#,10]]

The ‘english pseudocode’ matches the written code almost 1-to-1. It’s easier to write, understand, edit, and maintain.

This code is also easy to compose: the above chain is just a compound function, it hasn’t been applied yet. Therefore, we can just assign it a name:

MostFrequentWords=StringSplit/*ToLowerCase /* DeleteCases[trivialWords]/*Counts/*Sort/*Reverse/*Function[Take[#,10]];

We can also create a function that merely returns this comound function:

NMostFrequentWords[n_]:=StringSplit/*ToLowerCase /* DeleteCases[trivialWords]/*Counts/*Sort/*Reverse/*Function[Take[#,n]];

This compositional style can be very flexible and powerful in general.

Transparency

Representing functions using operator forms isn’t just syntactically cleaner than using pure functions (e.g. Select[PrimeQ] versus Select[#,PrimeQ]&).

Operator forms provide a symbolic language for describing operations abstractly. This unlocks powerful new idioms:

toSQLClause[Select[test_]]:= "WHERE " <> toSQLTest[test];

It is very difficult (and also conceptually undesirable) to use pure functions for this purpose.

Dataset queries are built on such symbolic manipulations of operator forms, as we will see.

Ordinary Tables

Dataset: some passengers on the Titanic

a table is a set of observations

each observation gives the values of observed properties

each observation is represented by an association, e.g:

\[LeftAssociation]"age"->59,"sex"->"female","survived"->True\[RightAssociation]

each key in the association corresponds to a column

allows easy mapping, selecting

extract individual rows to get single records

Indexed tables

Dataset: planets of the solar system

an indexed table is a set of unbiquely identified observations

each observation is associated/labeled with a key:

"Mercury"->\[LeftAssociation]"Mass"->(QuantityUnit[3.3010`6.*^23]),"Radius"->(QuantityUnit[2439.7`5.]),"Moons"->\[LeftAssociation]\[RightAssociation]\[RightAssociation]

allows simple lookup by key

key is preserved by sorting, selection, etc..

Anonymous tables

Dataset: soccer matches of the 2014 World Cup

columns in an anonymous table don’t have names

Examples: Constructing a Dataset

From raw data

Dataset @ Table[<|"n" -> n, "square" -> n^2, "prime" -> PrimeQ[n]|>,{n,100}]

From a file

FilePrint["ExampleData/countries-currency"]

SemanticImport["ExampleData/countries-currency"]

From the Wolfram Knowledgebase

EntityValue[LinguisticAssistant,{"GDP","Population","CountryCode"},"Dataset"]

Part syntax

Recap: Part syntax gives a general way to slice expressions.

Part[expr,p1,p2,…] gives the parts of  expr at the ‘addresses’  p1 at level 1,  p2 at level 2, etc.

In a list, the n’th element of a list has address  n.

For"key"->value in an association, value has address "key".

In a list or an association  All allows us to ‘preserve’ a level, focusing the rest of the slicing on all parts simulatenously.

Part operations are somewhat hard to describe precisely, but are very natural and intuitive once you know them.

a simple data structure (a list of pairs):

tallies=Tally[{a,b,b,b,b,a,a,a,a,a,b,c,d}]

extract the first element:

tallies[[1]]

extract the second element:

tallies[[2]]

extract the first element from each list:

tallies[[All,1]]

extract the second element from each list:

tallies[[All,2]]

Interaction with associations

strings are automatically treated as keys

a more complex data structure (association of lists):

table=<|
"age"->{23,43,62,34,55},"sex"->{"M","M","F","M","F"},
"employed"->{True,False,True,True,True}|>;

transpose it to get list of associations:

table2=Transpose[table,AllowedHeads->All]

(we will drop the need for this option in the future)

look up the age of the first person:

table2[[1,"age"]]

look up all the ages

table2[[All,"age"]]

get sub-associations:

table2[[All,{"age","sex"}]]

Generalizing to allow computations to be performed

Part allows us to describe concisely how to take a subset of an expression.

We can extend this idea to allow computations to be performed ‘along the way’.

New in 10, a hiearchical query language allows us to formalize this notion, and to use it on the data stored in a  Dataset.

Queries

How do we perform queries on Dataset?

in simple cases, queries are expressed (and behave) identically to Partspecifications.

titanic[All,"age"]

except queries can contain operators, as well:

titanic[Select[#age>30&],"survived"]

what do these operators do?

Parts and Levels

Levels refer to successively more deeply nested parts of expressions

Each part of a Dataset query refers to a deeper level of nesting:

Examples

Titanic

Load a dataset of passengers of the Titanic:

titanic=ExampleData[{"Dataset","Titanic"}]

Get the number of rows:

Length[titanic]

Get a random sample of passengers:

RandomSample[titanic,5]

The underlying data is a list of associations:

Normal[%]

Count the number of passengers with a missing age:

titanic[Count[_Missing],"age"]

Count the number of passengers in 1st, 2nd, and 3rd class:

titanic[Counts,"class"]

Get a histogram of passenger ages:

titanic[Histogram,"age"]

Get a histogram of passenger ages, grouped by passenger class:

titanic[GroupBy[Key["sex"]],GroupBy[Key["class"]],Histogram[#, {0,80,4}]&,"age"]

Find the age of the oldest passenger:

titanic[Max,"age"]

Calculate the overall survival ratio:

ratio[list_] := list // Boole//Mean//N;

titanic[ratio,"survived"]

Show the survival ratio against sex and passenger class:

titanic[GroupBy[Key["sex"]],GroupBy[Key["class"]],ratio,"survived"]

Show the survival ratio as a function of age:

titanic[GroupBy[Round[#age,5]&],ratio,"survived"]//ListPlot

Planets

Load a dataset of planets and their properties:

planets=ExampleData[{"Dataset","Planets"}]

Look up the mass of the Earth:

planets["Earth","Mass"]

Look up the moons of Neptune:

planets["Neptune","Moons"]

Produce a dataset of the radii of the planets:

planets[All,"Radius"]

Visualize the radii of the planets:

planets[BarChart,"Radius"]

Produce a dataset of the number of moons of each planet:

planets[All,"Moons",Length]

PieChart[%,ChartLegends->Automatic]

Obtain a list of only those moons that have a mass larger than half that of Earth's moon:

moonMass = planets["Earth","Moons","Moon","Mass"];
planets[All,"Moons",Select[#Mass > moonMass/2&]/*Keys]

Find the heaviest moon of each planet:

planets[All,"Moons",MaximalBy[Key["Mass"]]]

Obtain a list of all planetary moons:

moons=planets[Catenate,"Moons"]

Make a scatter plot of the mass against radius:

moons[ListLogLogPlot,{#Mass,#Radius}&]

Calculate and histogram the densities:

sphereVolume=LinguisticAssistant;
moons[Histogram,#Mass /sphereVolume[#Radius]&]

Compute the mean density for the moons of each planet:

planets[All,"Moons",Mean,#Mass/sphereVolume[#Radius]&]

Create a table comparing the density of each planet with the mean density of its moons:

density=#Mass/sphereVolume[#Radius]&;

planets[All,<|"Planet Density" -> density,"Moon Density"->Query["Moons",Mean,density]|>]

Subtleties

Ascending and descending operators

A sequence of operators such as [Select[…],"key",f] specifies an operation to do at each level.

But in what order should we do them?

For example, let’s take this simple dataset:

people=Dataset @ Transpose[<|
"age"->{23,43,62,34,55},"sex"->{"M","M","F","M","F"},
"employed"->{True,False,True,True,True}|>,AllowedHeads->All]

This simple query does what we expect:

people[Max,"age"]

But for this to have worked, we must have first taken the “age” values and then applied Max to them.

This next query requires the opposite behavior (to be efficient):

people[Select[#age>20&],"sex"]

Most operators is usually applied while ‘descending’: the operator is applied, and then subsequent operators are applied to deeper levels of the result. (e.g. Select).

Other operators are ‘ascending’: the result of subsequent operators is computed first, and then the operator is applied to that (e.g. Mean).

In general, operators that ‘preserve deeper levels’, such as Select, are descending, because we know we can keep descending into their output. In contrast, user functions (and functions like Total) generally speaking destroy any deeper structure, so they only make sense to apply while ‘ascending’.

You can chain descending and ascending operators, and they will be automatically split so as to make sense:

people[Select[#age>20&],"age"][Max]

In complex cases it can get confusing, hopefully in the future interactive suggestions and diagrams will make it easier to help you write queries.

This is the price we pay for something flexible that is modeled on Part syntax -- SQL suffers the same fate with complicated, nested joins.

Behind the scenes

Query compilation

Queries are translated into compositions of ordinary WL functions in order  to be executed -- I call this ‘compilation’.

Note this doesn’t refer to the Compile function or machine code. That kind of compilation will eventually be applied to Dataset queries, but isn’t yet.

Operator forms are ideal for this representation, because the individual operators are human readable, pattern matchable, and canonical.

By composing and nesting these operators and operator forms, we can represent complex queries in a way that is straightforward to execute.

Some simple examples

[All,f]  is equivalent to  Map[f]:

Query[All,f] @ {a,b,c}

Map[f] @ {a,b,c}

[Select[f],g]  is equivalent to  Select[f]/*Map[g]:

Query[Select[PrimeQ],g] @ {1,2,3,4,5}

Select[PrimeQ]/*Map[g] @ {1,2,3,4,5}

[All,"key"]  is equivalent to  Slice[All,"key"]:

Note that Slice is not a system symbol, yet. It is the operator form of Part, basically.

Query[All,"key"] @ {<|"key"->1|>,<|"key"->2|>}

Slice[All,"key"] @ {<|"key"->1|>,<|"key"->2|>}

Various such identities allow us to compile any query into an ordinary WL compound operator.

In V10.0.2, you can use Normal to find the compiled form of a Query:

Normal @ Query[All,f]

Normal @ Query[Select[f],g]

Normal @ Query[All,"key"]

Normal @ Query[Total,"key"]

Normal @ Query[Select[PrimeQ],"key"]

Query already optimizes these compiled forms in some cases, more optimizations will happen in the future.

Types

z=RandomSample[titanic,10]

InputForm[z]

Vector[Struct[{"class", "age", "sex", 
    "survived"}, {Atom[Enumeration["1st", "2nd", 
      "3rd"]], Atom[Integer], 
    Atom[Enumeration["female", "male"]], 
    Atom[Boolean]}], 10]

Dataset formats data that has a tabular structure, etc.

Dataset rejects queries that will fail

This happens using an algebraic type system that automatically models the data in your dataset.

If we have time I will show you...

The future of Dataset

Expressivity

Functions to ‘index’ and ‘deindex’ tables:

({{{
<|a->1,b->2,c->3|>,
<|a->4,b->5,c->6|>
},  ⟷ IndexBy[a] ⟷, <|
1-><|b->2,c->3|>,
4-> <|b->5,c->6|>
|>}})

Functions to make it easier to ‘join’ one table into another.

Missing system operator forms like Sliceand ApplyThrough.

ApplyThrough[{f,g,h}][1]

Optimization

Query planning

Data representation: column oriented storage

Interoperability

Mapping queries to SQL databases

Fast JSON Import/Export

Ergonomics

Predictive suggestions

Natural language queries

Automatic visualization